﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;
using System.Data.Common;

/// <summary>
/// 数据库操作接口类
/// </summary>
public class DBControl
{
    //数据库连接字符串
    string connstr = ConfigurationManager.AppSettings["connstr"];

    //获得数据库连接
    public DbConnection GetConn()
    {
        DbConnection conn = (new DBConnector()).GetConnection();
        return conn;
    }

    //无返回集的方法
    public int SqlExecuteNonQuery(string strCMD)
    {
        DbConnection conn = this.GetConn();
        DbCommand cmd = conn.CreateCommand();
        cmd.CommandText = strCMD;
       
        int iResult = cmd.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
        cmd.Dispose();
        return iResult;
    }

    //返回SqlDataReader对象
    public DbDataReader GetDataReader(string strCMD)
    {
        DbConnection conn = this.GetConn();
        DbCommand cmd = conn.CreateCommand();
        cmd.CommandText = strCMD;
        DbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return dr;
    }

    //返回数据集DataSet
    public DataSet GetDataSet(string strCMD,string strTableName)
    {
        DbDataAdapter da = (new DBConnector()).GetAdapter(strCMD);
        DataSet ds = new DataSet();
        da.Fill(ds,strTableName);
        da.Dispose();
        return ds;
    }

    public string DateTimeToString(DateTime time_to_convert)
    {
        return (new DBConnector()).DateTimeToString(time_to_convert);
    }

    //向数据库提交多个SQL命令，对数据进行事务管理（事务：这一批SQL命令要么都执行，都么都不执行）
    public bool ExecuteSQLs(ArrayList strCMDs)
    {
        bool bIsSuccess = true;
        DbConnection conn = this.GetConn();
        DbCommand cmd = conn.CreateCommand();
        DbTransaction trans = conn.BeginTransaction();//启动事务管理
        cmd.Connection = conn;
        cmd.Transaction = trans;
        try
        {
            foreach (string strCMD in strCMDs)
            {
                cmd.CommandText = strCMD;
                cmd.ExecuteNonQuery();
            }
            trans.Commit();//提交数据事务
        }
        catch
        {
            bIsSuccess = false;
            //发生异常后，利用事务的回滚方法撤销所有SQL命令，以保证数据的完整性
            trans.Rollback();
        }
        finally
        {
            conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }
        return bIsSuccess;
    }

    public bool TryGetInt(DataRow data_source, string column_name, ref int value)
    {
        bool result = false;
        object raw_value = data_source[column_name];
        if (raw_value != null && !(raw_value is DBNull) && (raw_value is IConvertible))
        {
            value = ((IConvertible)raw_value).ToInt32(null);
            result = true;            
        }
        return result;
    }

    public bool TryGetString(DataRow data_source, string column_name, ref string value)
    {
        bool result = false;
        object raw_value = data_source[column_name];
        if (raw_value != null && !(raw_value is DBNull) && (raw_value is string))
        {
            value = (string)raw_value;
            result = true;
        }
        return result;
    }

    public bool TryGetDateTime(DataRow data_source, string column_name, ref DateTime value)
    {
        bool result = false;
        object raw_value = data_source[column_name];
        if (raw_value != null && !(raw_value is DBNull) && (raw_value is DateTime))
        {
            value = (DateTime)raw_value;
            result = true;
        }
        return result;
    }
}
